USE [ServerMonitor]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_TruncateDBLog]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Sp_TruncateDBLog]
GO
USE [ServerMonitor]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Sp_TruncateDBLog]
AS
BEGIN
  SET NOCOUNT ON
  DECLARE @DBName NVARCHAR(256),@model VARCHAR(50),@Query NVARCHAR(4000)
  DECLARE DBCur CURSOR
  FOR
    SELECT name,CONVERT(VARCHAR(50),DATABASEPROPERTYEX(name,'RECOVERY')) AS model 
    FROM master..sysdatabases
    WHERE status <> 512 AND status <> 528
    AND name NOT IN ('tempdb','QuestSoftware','ReportServerTempDB')
  OPEN DBCur
  FETCH NEXT FROM DBCur INTO @DBName,@model
  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF(@model = 'FULL')
    BEGIN
      SET @Query = REPLACE('BACKUP LOG [#DBName#] WITH TRUNCATE_ONLY','#DBName#',@DBName)
      --PRINT @Query
      EXEC SP_EXECUTESQL @Query
    END

    FETCH NEXT FROM DBCur INTO @DBName,@model
  END
  CLOSE DBCur
  DEALLOCATE DBCur

  SET NOCOUNT OFF
END


GO


